/**
 * 
 */
package br.embedded.socialapp.data;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.Set;

import br.edu.ufcg.embedded.domain.CustomVertex;
import br.edu.ufcg.embedded.domain.DirectedGraphBuilder;
import br.edu.ufcg.embedded.util.FileManager;
import br.edu.ufcg.embedded.util.Util;

/**
 * @author daniel
 *
 */
public class FriendshipDAO
{	
	private static FriendshipDAO singleton;

	private FriendshipDAO()
	{
		ConfigurationReader.readAllParameters();
	}

	public static FriendshipDAO getInstance()
	{
		if (singleton == null)
			singleton = new FriendshipDAO();
		return singleton;
	}

	public synchronized void truncate()
	{
		Connection connection = null;
		Statement statement = null;

		try {
			Class.forName(ConfigurationReader.DRIVER);
			connection = DriverManager.getConnection(ConfigurationReader.CONNECTION, ConfigurationReader.USER, ConfigurationReader.PWD);
			statement = connection.createStatement();

			statement.execute("truncate table calculated_friendship;");
			//statement.execute("truncate table facebook;");

		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally
		{
			if (statement != null)
			{
				try {
					statement.close();
				}
				catch (SQLException sqlex)
				{}
			}
			if (connection != null)
			{
				try{
					connection.close();
				}
				catch(SQLException sqlex)
				{}
			}
		}
	}

	public synchronized void bulkInsert(List<String> files)
	{
		for (String file : files) {

			Connection connection = null;
			Statement statement = null;

			try {
				Class.forName(ConfigurationReader.DRIVER);
				connection = DriverManager.getConnection(ConfigurationReader.CONNECTION, ConfigurationReader.USER, ConfigurationReader.PWD);
				statement = connection.createStatement();

				statement.execute("COPY calculated_friendship FROM '" + file + "' WITH DELIMITER ';'");
				//statement.execute("COPY dataset FROM '" + file + "' WITH DELIMITER ';'");

			} catch (ClassNotFoundException e) {
				e.printStackTrace();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			finally
			{
				if (statement != null)
				{
					try {
						statement.close();
					}
					catch (SQLException sqlex)
					{}
				}
				if (connection != null)
				{
					try{
						connection.close();
					}
					catch(SQLException sqlex)
					{}
				}

				FileManager.delete(file);
			}
		}
	}
	
	public synchronized DirectedGraphBuilder getSocialConnections(Collection<Integer> users)
	{
		DirectedGraphBuilder graph = new DirectedGraphBuilder();

		Connection connection = null;
		Statement statement = null;

		try {
			Class.forName(ConfigurationReader.DRIVER);
			connection = DriverManager.getConnection(ConfigurationReader.CONNECTION, ConfigurationReader.USER, ConfigurationReader.PWD);
			statement = connection.createStatement();
			
			Util<Integer> util = new Util<Integer>();
			String l = util.getStringfrom(users);
			
			StringBuilder sb = new StringBuilder();
			sb.append("select user1 as u1, user2 as u2 from dataset where user2 in (");
			sb.append(l);
			//sb.append(") union all select user2 as u1, user1 as u2 from dataset2 where user2 in (");
			//sb.append(l);
			sb.append(");");
			
			l = null;
			System.out.println(sb.toString());
			ResultSet resultSet = statement.executeQuery(sb.toString());
			
			sb = null;
			
			System.out.println("aqui");
			
			while (resultSet.next()) {
				
				String v1 = Integer.toString(resultSet.getInt("u1"));
				String v2 = Integer.toString(resultSet.getInt("u2"));
				
				graph.add(v1);
				graph.add(v2);
				graph.addEdge(v1, v2);
			}

		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally
		{
			if (statement != null)
			{
				try {
					statement.close();
				}
				catch (SQLException sqlex)
				{}
			}
			if (connection != null)
			{
				try{
					connection.close();
				}
				catch(SQLException sqlex)
				{}
			}
		}

		return graph;
	}

	public synchronized DirectedGraphBuilder getSocialConnections()
	{
		DirectedGraphBuilder graph = new DirectedGraphBuilder();

		Connection connection = null;
		Statement statement = null;

		try {
			Class.forName(ConfigurationReader.DRIVER);
			connection = DriverManager.getConnection(ConfigurationReader.CONNECTION, ConfigurationReader.USER, ConfigurationReader.PWD);
			statement = connection.createStatement();

			ResultSet resultSet = statement.executeQuery("select * from dataset;");

			while (resultSet.next()) {
				
				String v1 = Integer.toString(resultSet.getInt("user1")); 
				String v2 = Integer.toString(resultSet.getInt("user2"));
				
				graph.add(v1);
				graph.add(v2);
				graph.addEdge(v1, v2);
			}

		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally
		{
			if (statement != null)
			{
				try {
					statement.close();
				}
				catch (SQLException sqlex)
				{}
			}
			if (connection != null)
			{
				try{
					connection.close();
				}
				catch(SQLException sqlex)
				{}
			}
		}

		return graph;
	}

	public List<CustomVertex> getRandomVertices(String classification, int count) {

		List<CustomVertex> list = new ArrayList<CustomVertex>();

		Connection connection = null;
		PreparedStatement statement = null;

		try {
			Class.forName(ConfigurationReader.DRIVER);
			connection = DriverManager.getConnection(ConfigurationReader.CONNECTION, ConfigurationReader.USER, ConfigurationReader.PWD);

			statement = connection.prepareStatement("select user1, classification from dataset_users where classification = ? ORDER BY RANDOM() LIMIT "+ count +";");
			statement.setString(1, classification);
			//statement = connection.prepareStatement("select user1, classification from dataset_users where user1 = ? ORDER BY RANDOM() LIMIT "+ count +";");
			//statement.setInt(1, 269349);
			
			ResultSet resultSet = statement.executeQuery();

			while (resultSet.next()) {

				list.add(new CustomVertex(resultSet.getInt("user1"), resultSet.getString("classification"), null));
			}

		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally
		{
			if (statement != null)
			{
				try {
					statement.close();
				}
				catch (SQLException sqlex)
				{}
			}
			if (connection != null)
			{
				try{
					connection.close();
				}
				catch(SQLException sqlex)
				{}
			}
		}

		return list;
	}

	public List<CustomVertex> getFollowers(CustomVertex vertex) {

		List<CustomVertex> list = new ArrayList<CustomVertex>();

		Connection connection = null;
		PreparedStatement statement = null;

		try {
			Class.forName(ConfigurationReader.DRIVER);
			connection = DriverManager.getConnection(ConfigurationReader.CONNECTION, ConfigurationReader.USER, ConfigurationReader.PWD);

			statement = connection.prepareStatement("select user1, classification from dataset_users where user1 in (select user1 from dataset2 where user2 = ?);");

			statement.setInt(1, vertex.getNumber());

			ResultSet resultSet = statement.executeQuery();

			while (resultSet.next()) {

				CustomVertex customVertex = new CustomVertex(resultSet.getInt("user1"), 
						resultSet.getString("classification"), vertex.toString());

				list.add(customVertex);
			}

		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally
		{
			if (statement != null)
			{
				try {
					statement.close();
				}
				catch (SQLException sqlex)
				{}
			}
			if (connection != null)
			{
				try{
					connection.close();
				}
				catch(SQLException sqlex)
				{}
			}
		}

		return list;
	}

	public List<CustomVertex> getFollowing(CustomVertex vertex) {

		List<CustomVertex> list = new ArrayList<CustomVertex>();

		Connection connection = null;
		PreparedStatement statement = null;

		try {
			Class.forName(ConfigurationReader.DRIVER);
			connection = DriverManager.getConnection(ConfigurationReader.CONNECTION, ConfigurationReader.USER, ConfigurationReader.PWD);

			statement = connection.prepareStatement("select user1, classification from dataset_users where user1 in (select user2 from dataset where user1 = ?);");

			statement.setInt(1, vertex.getNumber());

			ResultSet resultSet = statement.executeQuery();

			while (resultSet.next()) {

				CustomVertex customVertex = new CustomVertex(resultSet.getInt("user1"), 
						resultSet.getString("classification"), vertex.toString());

				list.add(customVertex);
			}

		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally
		{
			if (statement != null)
			{
				try {
					statement.close();
				}
				catch (SQLException sqlex)
				{}
			}
			if (connection != null)
			{
				try{
					connection.close();
				}
				catch(SQLException sqlex)
				{}
			}
		}

		return list;
	}
}
